using SPI.AgentServer.Services.Execution;
using System.Text;

namespace SPI.AgentServer.Services.Tools;

/// <summary>
/// 在校人数统计工具 - 统计进校人数、在校人数等
/// 覆盖场景：P-003 今天进校人数, P-004 当前在校人数, P-009 各部门进校统计
/// </summary>
public class GetCampusStatsTool : IQueryTool
{
    private readonly IDatabaseExecutor _executor;
    private readonly ILogger<GetCampusStatsTool> _logger;

    public string Name => "get_campus_stats";
    public string Description => "统计在校人数相关信息，包括今天进校人数、当前在校人数、各部门统计等。用于回答'今天多少人进校'、'现在有多少人在校'、'各部门人数统计'等问题。";

    public GetCampusStatsTool(
        IDatabaseExecutor executor,
        ILogger<GetCampusStatsTool> logger)
    {
        _executor = executor;
        _logger = logger;
    }

    public bool ValidateParameters(Dictionary<string, object> parameters, out List<string> errors)
    {
        errors = new List<string>();
        return true;
    }

    public async Task<ToolResult> ExecuteAsync(Dictionary<string, object> parameters, CancellationToken cancellationToken = default)
    {
        if (parameters == null)
        {
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { "参数不能为空" }
            };
        }

        try
        {
            // 获取参数
            string statsType = parameters.ContainsKey("statsType") ? parameters["statsType"]?.ToString() ?? "entry_count" : "entry_count";
            string? queryDate = parameters.ContainsKey("queryDate") ? parameters["queryDate"]?.ToString() : null;
            bool byDepartment = parameters.ContainsKey("byDepartment") && Convert.ToBoolean(parameters["byDepartment"]);

            // 默认查询今天
            if (string.IsNullOrEmpty(queryDate))
            {
                queryDate = DateTime.Today.ToString("yyyy-MM-dd");
            }

            // 构建SQL
            var sql = statsType switch
            {
                "on_campus" => BuildOnCampusCountQuery(queryDate),
                "by_department" => BuildDepartmentStatsQuery(queryDate),
                _ => BuildEntryCountQuery(queryDate)
            };

            _logger.LogInformation("Executing campus stats query with SQL: {Sql}", sql);

            var result = await _executor.ExecuteQueryAsync(sql, null, cancellationToken);

            return new ToolResult
            {
                Success = true,
                Data = result.Data,
                Message = "在校人数统计查询成功",
                Metadata = new Dictionary<string, object>
                {
                    ["executionTimeMs"] = result.ExecutionTimeMs,
                    ["executedSql"] = sql,
                    ["statsType"] = statsType,
                    ["queryDate"] = queryDate
                }
            };
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error executing campus stats query");
            return new ToolResult
            {
                Success = false,
                Errors = new List<string> { $"查询失败: {ex.Message}" }
            };
        }
    }

    /// <summary>
    /// 构建进校人数统计查询
    /// </summary>
    private string BuildEntryCountQuery(string queryDate)
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    COUNT(DISTINCT XGH) AS entry_count,");
        sb.AppendLine($"    '{queryDate}' AS query_date");
        sb.AppendLine("FROM TblGateInformation");
        sb.AppendLine($"WHERE DATE(SJ) = '{queryDate}'");
        sb.AppendLine("    AND CRBS = 1"); // 1=进

        return sb.ToString();
    }

    /// <summary>
    /// 构建当前在校人数查询
    /// </summary>
    private string BuildOnCampusCountQuery(string queryDate)
    {
        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    COUNT(DISTINCT XGH) AS on_campus_count,");
        sb.AppendLine($"    '{queryDate}' AS query_date");
        sb.AppendLine("FROM (");
        sb.AppendLine("    SELECT");
        sb.AppendLine("        XGH,");
        sb.AppendLine("        SUM(CASE WHEN CRBS = 1 THEN 1 ELSE -1 END) AS net_status");
        sb.AppendLine("    FROM TblGateInformation");
        sb.AppendLine($"    WHERE DATE(SJ) = '{queryDate}'");
        sb.AppendLine("    GROUP BY XGH");
        sb.AppendLine(") t");
        sb.AppendLine("WHERE net_status > 0"); // 净进入次数大于0表示在校

        return sb.ToString();
    }

    /// <summary>
    /// 构建各部门进校统计查询
    /// </summary>
    private string BuildDepartmentStatsQuery(string queryDate)
    {
        // 获取当前月份的员工表名
        var currentMonth = DateTime.Parse(queryDate).ToString("yyyyMM");
        var employeeTable = $"TblEmployee_{currentMonth}01";

        var sb = new StringBuilder();

        sb.AppendLine("SELECT");
        sb.AppendLine("    e.Department AS department,");
        sb.AppendLine("    COUNT(DISTINCT g.XGH) AS entry_count,");
        sb.AppendLine($"    '{queryDate}' AS query_date");
        sb.AppendLine("FROM TblGateInformation g");
        sb.AppendLine($"LEFT JOIN {employeeTable} e ON g.XGH = e.EmployeeNumber");
        sb.AppendLine($"WHERE DATE(g.SJ) = '{queryDate}'");
        sb.AppendLine("    AND g.CRBS = 1"); // 1=进
        sb.AppendLine("    AND e.Department IS NOT NULL");
        sb.AppendLine("GROUP BY e.Department");
        sb.AppendLine("ORDER BY entry_count DESC");

        return sb.ToString();
    }
}
